import 'dart:math';

import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';

import 'info_button.dart';

class UpateTest extends StatefulWidget {
  const UpateTest({super.key});

  @override
  State<UpateTest> createState() => _UpateTestState();
}

class _UpateTestState extends State<UpateTest> {
  List<InfoButton> btnArray = <InfoButton>[];
  Database? _database;
  String? _openDbResult;
  String? _createTableResult;
  String? _insertResult;
  String? _queryResult;
  String? _updateResult;
  String? _updateResultByRaw;
  String? _updateResultByRaw1;
  String? _deleteResult;
  String? _deleteResult1;
  final String _tableName = "mh_habits";
  late String _dbName;
  String uuid = '';

  @override
  void initState() {
    _dbName = "${Random().nextInt(36000)}.db";
    _openDB();
    super.initState();
  }

  void _openDB() async {
    try {
      _database = await openDatabase(
        _dbName,
        onCreate: (db, version) async {
          await db.execute('''CREATE TABLE IF NOT EXISTS $_tableName (
    id_ INTEGER PRIMARY KEY AUTOINCREMENT,
    type_ INTEGER NOT NULL,
    create_t INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') as int)),
    modify_t INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') as int)),
    uuid TEXT NOT NULL UNIQUE,
    status INTEGER NOT NULL,
    name TEXT,
    desc TEXT,
    color INTEGER,
    daily_goal REAL NOT NULL,
    daily_goal_unit TEXT NOT NULL,
    daily_goal_extra REAL,
    freq_type INTEGER,
    freq_custom TEXT,
    start_date INTEGER NOT NULL,
    target_days INTEGER,
    remind_cutsom TEXT,
    remind_question TEXT,
    sort_position REAL NOT NULL DEFAULT 9e999
)''');
          await db.execute(
            '''
                CREATE TRIGGER item_update_trigger
                AFTER UPDATE ON $_tableName
                BEGIN
                  UPDATE $_tableName 
                  SET name = '触发器更改名称' 
                  WHERE uuid = NEW.uuid;
                END
                ''',
          );
        },
        version: 1,
      );
      final path = await getDatabasesPath();
      setState(() {
        _openDbResult = "打开数据成功：$path";
      });
    } catch (error) {
      setState(() {
        _openDbResult = error.toString();
      });
    }
  }

  ///打开数据库
  Widget _openDbWidget() {
    return InfoButton(
      title: "打开数据库",
      info: _openDbResult,
      onTap: () async {},
    );
  }

  ///创建表
  Widget _createTableWidget() {
    return InfoButton(
      title: "创建表",
      info: _createTableResult,
      onTap: () async {
        if (_database != null) {
          try {
            await _database!.execute('''CREATE TABLE IF NOT EXISTS $_tableName (
    id_ INTEGER PRIMARY KEY AUTOINCREMENT,
    type_ INTEGER NOT NULL,
    create_t INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') as int)),
    modify_t INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') as int)),
    uuid TEXT NOT NULL UNIQUE,
    status INTEGER NOT NULL,
    name TEXT,
    desc TEXT,
    color INTEGER,
    daily_goal REAL NOT NULL,
    daily_goal_unit TEXT NOT NULL,
    daily_goal_extra REAL,
    freq_type INTEGER,
    freq_custom TEXT,
    start_date INTEGER NOT NULL,
    target_days INTEGER,
    remind_cutsom TEXT,
    remind_question TEXT,
    sort_position REAL NOT NULL DEFAULT 9e999
)''');
            setState(() {
              _createTableResult = "创建表成功";
            });
          } catch (error) {
            setState(() {
              _createTableResult = error.toString();
            });
          }
        }
      },
    );
  }

  ///插入数据
  Widget _insertDataWidget() {
    return InfoButton(
      title: "插入数据",
      info: _insertResult,
      onTap: () async {
        if (_database != null) {
          try {
            //获取当前时间戳
            int now = DateTime.now().millisecondsSinceEpoch ~/ 1000;
            //插入数据
            final Map<String, dynamic> data = {
              'type_': Random().nextInt(30000),
              'create_t': now,
              'modify_t': now,
              'uuid': Random().nextInt(30000).toInt().toString(),
              'status': 1,
              'name': 'Jack',
              'desc': 'uewoqiusjkashdwqioeuasdjkh',
              'color': 1,
              'daily_goal': 1.968,
              'daily_goal_unit': 2.3679,
              'daily_goal_extra': 1.2345,
              'freq_type': 1,
              'freq_custom': '1,2,3',
              'start_date': now,
              'target_days': 30,
              'remind_cutsom': '1,2,3',
              'remind_question': '你好',
              'sort_position': 1.2345
            };
            final id = await _database!.insert(_tableName, data);
            setState(() {
              _insertResult = "插入数据成功：id=$id";
            });
          } catch (error) {
            setState(() {
              _insertResult = error.toString();
            });
          }
        }
      },
    );
  }

  ///查询数据
  Widget _queryWidget() {
    return InfoButton(
      title: "查询数据",
      info: _queryResult,
      onTap: () async {
        if (_database != null) {
          final maps = await _database!.query(_tableName);
          uuid = maps.first["uuid"] as String;
          print("uuid--------------:$uuid");
          setState(() {
            _queryResult = maps.map((map) => map.toString()).join("\n");
          });
        }
      },
    );
  }

  ///更新数据
  Widget _updateDataWidget() {
    return InfoButton(
      title: "更新数据",
      info: _updateResult,
      onTap: () async {
        if (_database != null) {
          try {
            final changeRows =
                await _database!.update(_tableName, {'name': 'Maco', 'desc': '这是更新后的描述'}, where: 'uuid = ?', whereArgs: [uuid]);
            setState(() {
              _updateResult = "更新了$changeRows行数据";
            });
          } catch (error) {
            setState(() {
              _updateResult = error.toString();
            });
          }
        }
      },
    );
  }

  ///更新数据
  Widget _updateDataByRawWidget() {
    //触发器
    final trgger = '''
        CREATE TRIGGER item_update_trigger
        AFTER UPDATE ON $_tableName
        BEGIN
          UPDATE $_tableName SET name = '触发器更改名称' WHERE uuid = $uuid;
        END;
        ''';
    return InfoButton(
      title: "更新数据-raw- 触发器方式",
      info: _updateResultByRaw,
      onTap: () async {
        if (_database != null) {
          try {
            final changeRows = await _database!
                .rawUpdate('UPDATE $_tableName SET name = ?, desc = ? WHERE uuid = ?', ['Marry', '这是不带回滚操作的描述更新', uuid]);
            // await _database!.execute(trgger);
            setState(() {
              _updateResultByRaw = "更新了$changeRows行数据";
              // _updateResultByRaw = "更新了数据";
            });
          } catch (error) {
            setState(() {
              _updateResultByRaw = error.toString();
            });
          }
        }
      },
    );
  }

  ///更新数据
  Widget _updateDataByRawWidget1() {
    return InfoButton(
      title: "更新数据-raw",
      info: _updateResultByRaw1,
      onTap: () async {
        if (_database != null) {
          try {
            final changeRows = await _database!.rawUpdate(
                'UPDATE OR ROLLBACK $_tableName SET name = ?, desc = ? WHERE uuid = ?', ['Marry', '这是带回滚操作的描述更新', uuid]);
            setState(() {
              _updateResultByRaw1 = "更新了$changeRows行数据";
            });
          } catch (error) {
            setState(() {
              _updateResultByRaw1 = error.toString();
            });
          }
        }
      },
    );
  }

  Widget _deleteDataByRawWidget() {
    return InfoButton(
      title: "删除数据-raw",
      info: _deleteResult,
      onTap: () async {
        if (_database != null) {
          try {
            int count = await _database!.rawDelete("DELETE FROM $_tableName WHERE id_ = ?", [2]);
            setState(() {
              _deleteResult = "删除成功，删除了$count行数据";
            });
          } catch (e) {
            setState(() {
              _deleteResult = e.toString();
            });
          }
        }
      },
    );
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        centerTitle: true,
        title: const Text("触发器"),
      ),
      body: ListView(
        children: [
          // //打开数据库
          // _openDbWidget(),

          // //创建表
          // _createTableWidget(),

          //插入数据
          _insertDataWidget(),

          //查询数据
          _queryWidget(),

          //更新数据
          _updateDataWidget(),
          //更新数据-raw
          // _updateDataByRawWidget(),
          //更新数据-raw
          // _updateDataByRawWidget1(),

          //删除数据
          _deleteDataByRawWidget(),
        ],
      ),
    );
  }
}
